//-----------------------------------------------------------------------
// <copyright file="CompanyDAOBase.cs" company="FetCo">
//     Copyright (c) FetCo. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using FetCo.Framework;
using FetCo.Framework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ADESSA.Entities;

namespace ADESSA.Data
{    
    public abstract class CompanyDAOBase
    {
        #region Common methods
        public virtual Company CreateCompanyFromReader(IDataReader reader)
        {
            Company item = new Company();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("CompanyID"))) item.CompanyID = (long)reader["CompanyID"];
                if (!reader.IsDBNull(reader.GetOrdinal("MemberID"))) item.MemberID = (long)reader["MemberID"];
                if (!reader.IsDBNull(reader.GetOrdinal("CompanyName"))) item.CompanyName = (string)reader["CompanyName"];
                if (!reader.IsDBNull(reader.GetOrdinal("Country"))) item.Country = (long)reader["Country"];
                if (!reader.IsDBNull(reader.GetOrdinal("ZipCode"))) item.ZipCode = (string)reader["ZipCode"];
                if (!reader.IsDBNull(reader.GetOrdinal("Logo"))) item.Logo = (string)reader["Logo"];
                if (!reader.IsDBNull(reader.GetOrdinal("Address"))) item.Address = (string)reader["Address"];
                if (!reader.IsDBNull(reader.GetOrdinal("YearEstablishment"))) item.YearEstablishment = (string)reader["YearEstablishment"];
                if (!reader.IsDBNull(reader.GetOrdinal("PrimaryPro"))) item.PrimaryPro = (string)reader["PrimaryPro"];
                if (!reader.IsDBNull(reader.GetOrdinal("RepresentativeName"))) item.RepresentativeName = (string)reader["RepresentativeName"];
                if (!reader.IsDBNull(reader.GetOrdinal("SexRep"))) item.SexRep = (int)reader["SexRep"];
                if (!reader.IsDBNull(reader.GetOrdinal("JobTitleRep"))) item.JobTitleRep = (string)reader["JobTitleRep"];
                if (!reader.IsDBNull(reader.GetOrdinal("ContactPerson"))) item.ContactPerson = (string)reader["ContactPerson"];
                if (!reader.IsDBNull(reader.GetOrdinal("JobTitleContact"))) item.JobTitleContact = (string)reader["JobTitleContact"];
                if (!reader.IsDBNull(reader.GetOrdinal("SexContact"))) item.SexContact = (int)reader["SexContact"];
                if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) item.Phone = (string)reader["Phone"];
                if (!reader.IsDBNull(reader.GetOrdinal("Fax"))) item.Fax = (string)reader["Fax"];
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) item.Email = (string)reader["Email"];
                if (!reader.IsDBNull(reader.GetOrdinal("Web"))) item.Web = (string)reader["Web"];
                if (!reader.IsDBNull(reader.GetOrdinal("ProCateID"))) item.ProCateID = (long)reader["ProCateID"];
                if (!reader.IsDBNull(reader.GetOrdinal("Buy"))) item.Buy = (bool)reader["Buy"];
                if (!reader.IsDBNull(reader.GetOrdinal("Sell"))) item.Sell = (bool)reader["Sell"];
                if (!reader.IsDBNull(reader.GetOrdinal("TypeCompanyID"))) item.TypeCompanyID = (int)reader["TypeCompanyID"];
                if (!reader.IsDBNull(reader.GetOrdinal("TypeBusinessID"))) item.TypeBusinessID = (int)reader["TypeBusinessID"];
                if (!reader.IsDBNull(reader.GetOrdinal("MajorMarketID"))) item.MajorMarketID = (int)reader["MajorMarketID"];
                if (!reader.IsDBNull(reader.GetOrdinal("RegisteredCapital"))) item.RegisteredCapital = (string)reader["RegisteredCapital"];
                if (!reader.IsDBNull(reader.GetOrdinal("Turnover"))) item.Turnover = (string)reader["Turnover"];
                if (!reader.IsDBNull(reader.GetOrdinal("ExportTurnover"))) item.ExportTurnover = (string)reader["ExportTurnover"];
                if (!reader.IsDBNull(reader.GetOrdinal("NumberEmployees"))) item.NumberEmployees = (string)reader["NumberEmployees"];
                if (!reader.IsDBNull(reader.GetOrdinal("CertificateID"))) item.CertificateID = (int)reader["CertificateID"];
                if (!reader.IsDBNull(reader.GetOrdinal("Capacity"))) item.Capacity = (string)reader["Capacity"];
                if (!reader.IsDBNull(reader.GetOrdinal("CompanyInfoHome"))) item.CompanyInfoHome = (string)reader["CompanyInfoHome"];
                if (!reader.IsDBNull(reader.GetOrdinal("Other"))) item.Other = (string)reader["Other"];
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateCompanyFromReaderException, ex);
            }
            return item;
        }
        #endregion
        
        #region CreateCompany methods
            
        public virtual void CreateCompany(Company company)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyInsert");
                
                database.AddInParameter(dbCommand, "@MemberID", DbType.Int64, company.MemberID);
                database.AddInParameter(dbCommand, "@CompanyName", DbType.String, company.CompanyName);
                database.AddInParameter(dbCommand, "@Country", DbType.Int64, company.Country);
                database.AddInParameter(dbCommand, "@ZipCode", DbType.String, company.ZipCode);
                database.AddInParameter(dbCommand, "@Logo", DbType.String, company.Logo);
                database.AddInParameter(dbCommand, "@Address", DbType.String, company.Address);
                database.AddInParameter(dbCommand, "@YearEstablishment", DbType.String, company.YearEstablishment);
                database.AddInParameter(dbCommand, "@PrimaryPro", DbType.String, company.PrimaryPro);
                database.AddInParameter(dbCommand, "@RepresentativeName", DbType.String, company.RepresentativeName);
                database.AddInParameter(dbCommand, "@SexRep", DbType.Int32, company.SexRep);
                database.AddInParameter(dbCommand, "@JobTitleRep", DbType.String, company.JobTitleRep);
                database.AddInParameter(dbCommand, "@ContactPerson", DbType.String, company.ContactPerson);
                database.AddInParameter(dbCommand, "@JobTitleContact", DbType.String, company.JobTitleContact);
                database.AddInParameter(dbCommand, "@SexContact", DbType.Int32, company.SexContact);
                database.AddInParameter(dbCommand, "@Phone", DbType.String, company.Phone);
                database.AddInParameter(dbCommand, "@Fax", DbType.String, company.Fax);
                database.AddInParameter(dbCommand, "@Email", DbType.String, company.Email);
                database.AddInParameter(dbCommand, "@Web", DbType.String, company.Web);
                database.AddInParameter(dbCommand, "@ProCateID", DbType.Int64, company.ProCateID);
                database.AddInParameter(dbCommand, "@Buy", DbType.Boolean, company.Buy);
                database.AddInParameter(dbCommand, "@Sell", DbType.Boolean, company.Sell);
                database.AddInParameter(dbCommand, "@TypeCompanyID", DbType.Int32, company.TypeCompanyID);
                database.AddInParameter(dbCommand, "@TypeBusinessID", DbType.Int32, company.TypeBusinessID);
                database.AddInParameter(dbCommand, "@MajorMarketID", DbType.Int32, company.MajorMarketID);
                database.AddInParameter(dbCommand, "@RegisteredCapital", DbType.String, company.RegisteredCapital);
                database.AddInParameter(dbCommand, "@Turnover", DbType.String, company.Turnover);
                database.AddInParameter(dbCommand, "@ExportTurnover", DbType.String, company.ExportTurnover);
                database.AddInParameter(dbCommand, "@NumberEmployees", DbType.String, company.NumberEmployees);
                database.AddInParameter(dbCommand, "@CertificateID", DbType.Int32, company.CertificateID);
                database.AddInParameter(dbCommand, "@Capacity", DbType.String, company.Capacity);
                database.AddInParameter(dbCommand, "@CompanyInfoHome", DbType.String, company.CompanyInfoHome);
                database.AddInParameter(dbCommand, "@Other", DbType.String, company.Other);
                database.AddOutParameter(dbCommand, "@CompanyID", DbType.Int64, 8);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommand);
                company.CompanyID = (long)database.GetParameterValue(dbCommand, "@CompanyID");
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateCompanyException, ex);
            }
        }

        #endregion

        #region UpdateCompany methods
        
        public virtual void UpdateCompany(Company company)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyUpdate");            
                
                database.AddInParameter(dbCommand, "@CompanyID", DbType.Int64, company.CompanyID);
                database.AddInParameter(dbCommand, "@MemberID", DbType.Int64, company.MemberID);
                database.AddInParameter(dbCommand, "@CompanyName", DbType.String, company.CompanyName);
                database.AddInParameter(dbCommand, "@Country", DbType.Int64, company.Country);
                database.AddInParameter(dbCommand, "@ZipCode", DbType.String, company.ZipCode);
                database.AddInParameter(dbCommand, "@Logo", DbType.String, company.Logo);
                database.AddInParameter(dbCommand, "@Address", DbType.String, company.Address);
                database.AddInParameter(dbCommand, "@YearEstablishment", DbType.String, company.YearEstablishment);
                database.AddInParameter(dbCommand, "@PrimaryPro", DbType.String, company.PrimaryPro);
                database.AddInParameter(dbCommand, "@RepresentativeName", DbType.String, company.RepresentativeName);
                database.AddInParameter(dbCommand, "@SexRep", DbType.Int32, company.SexRep);
                database.AddInParameter(dbCommand, "@JobTitleRep", DbType.String, company.JobTitleRep);
                database.AddInParameter(dbCommand, "@ContactPerson", DbType.String, company.ContactPerson);
                database.AddInParameter(dbCommand, "@JobTitleContact", DbType.String, company.JobTitleContact);
                database.AddInParameter(dbCommand, "@SexContact", DbType.Int32, company.SexContact);
                database.AddInParameter(dbCommand, "@Phone", DbType.String, company.Phone);
                database.AddInParameter(dbCommand, "@Fax", DbType.String, company.Fax);
                database.AddInParameter(dbCommand, "@Email", DbType.String, company.Email);
                database.AddInParameter(dbCommand, "@Web", DbType.String, company.Web);
                database.AddInParameter(dbCommand, "@ProCateID", DbType.Int64, company.ProCateID);
                database.AddInParameter(dbCommand, "@Buy", DbType.Boolean, company.Buy);
                database.AddInParameter(dbCommand, "@Sell", DbType.Boolean, company.Sell);
                database.AddInParameter(dbCommand, "@TypeCompanyID", DbType.Int32, company.TypeCompanyID);
                database.AddInParameter(dbCommand, "@TypeBusinessID", DbType.Int32, company.TypeBusinessID);
                database.AddInParameter(dbCommand, "@MajorMarketID", DbType.Int32, company.MajorMarketID);
                database.AddInParameter(dbCommand, "@RegisteredCapital", DbType.String, company.RegisteredCapital);
                database.AddInParameter(dbCommand, "@Turnover", DbType.String, company.Turnover);
                database.AddInParameter(dbCommand, "@ExportTurnover", DbType.String, company.ExportTurnover);
                database.AddInParameter(dbCommand, "@NumberEmployees", DbType.String, company.NumberEmployees);
                database.AddInParameter(dbCommand, "@CertificateID", DbType.Int32, company.CertificateID);
                database.AddInParameter(dbCommand, "@Capacity", DbType.String, company.Capacity);
                database.AddInParameter(dbCommand, "@CompanyInfoHome", DbType.String, company.CompanyInfoHome);
                database.AddInParameter(dbCommand, "@Other", DbType.String, company.Other);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommand);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessUpdateCompanyException, ex);
            }
        }
        
        #endregion

        #region DeleteCompany methods
        public virtual void DeleteCompany(long companyID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyDelete");
                
                database.AddInParameter(dbCommand, "@CompanyID", DbType.Int64, companyID);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommand);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessDeleteCompanyException, ex);
            }
        }

        #endregion

        #region GetCompany methods
        
        public virtual Company GetCompany(long companyID)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyGet");
                
                database.AddInParameter(dbCommand, "@CompanyID", DbType.Int64, companyID);
                
                Company company =  null;
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommand))
                {
                    if (reader.Read())
                    {
                        company = CreateCompanyFromReader(reader);
                        reader.Close();
                    }
                }
                return company;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetCompanyException, ex);
            }
        }
        
        #endregion

        #region GetCompanyList methods
        public virtual CompanyCollection GetCompanyList(CompanyColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyGetList");
                
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 4);
                
                CompanyCollection companyCollection = new CompanyCollection();
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommand))
                {
                    while (reader.Read())
                    {
                        Company company = CreateCompanyFromReader(reader);
                        companyCollection.Add(company);
                    }
                    reader.Close();
                }
                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
                return companyCollection;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetCompanyListException, ex);
            }
        }
        
        public virtual CompanyCollection GetCompanyList(CompanyColumns orderBy, OrderDirection orderDirection)
        {            
            int totalRecords = 0;
            return GetCompanyList(orderBy, orderDirection, 0, 0, out totalRecords);
        }
        
        #endregion
		
		  #region GetCompanyList methods
        public virtual CompanyCollection GetCompanyListByParam(object orderBy, OrderDirection orderDirection, int page, int pageSize,string Criteria, out int totalRecords)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand("spCompanyGetListByParam");
                
                database.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy.ToString());
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
				database.AddInParameter(dbCommand, "@Criteria",  DbType.String, Criteria);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 4);
                
                CompanyCollection companyCollection = new CompanyCollection();
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommand))
                {
                    while (reader.Read())
                    {
                        Company company = CreateCompanyFromReader(reader);
                        companyCollection.Add(company);
                    }
                    reader.Close();
                }
                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");
                return companyCollection;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetCompanyListException, ex);
            }
        }
        
        public virtual CompanyCollection GetCompanyListByParam(object orderBy, OrderDirection orderDirection,string Criteria)
        {            
            int totalRecords = 0;
            return GetCompanyListByParam(orderBy, orderDirection, 0, 0,Criteria, out totalRecords);
        }
        
        #endregion
    }
}